Release 10.1A: OpenEdge Getting Started:
Database Essentials

Defining Indexes

Like a book index, which helps a reader retrieve information on a topic quickly, an index on a database table speeds up the process of searching and sorting rows. Although it is possible to search and sort data without using indexes, indexes generally speed up data access. Use them to avoid or limit row scanning operations and to avoid sorting operations. If you frequently search and sort row data by particular columns, you might want to create indexes on those columns. Or, if you regularly join tables to retrieve data, consider creating indexes on the common columns.

On the other hand, indexes consume disk space and add to the processing overhead of many data operations including data entry, backup, and other common administration tasks. Each time you update an indexed column, OpenEdge updates the index, and related indexes as well. When you create or delete a row, OpenEdge updates each index on the affected tables.

As you move into the details of index design, keep in mind that index design is not a once-only operation. It is a process, and it is intricately related to your coding practices. Faulty code can thwart any index scheme, and masterfully coded queries perform poorly if not properly supported by indexes. Therefore, as your applications develop and evolve, your indexing scheme might need to evolve as well. This discusses indexes in detail in the following sections:

Indexing basics
Choosing which tables and columns to index
Indexes and ROWIDs
Calculating index size
Eliminating redundant indexes
Deactivating indexes